i have a problem with a date parameter query using 'between' operator in oracle view, when i do this query :
SELECT *
FROM MY_VIEW
WHERE STATUS = 'Active'
AND CHECKER_DATE BETWEEN to_date(sysdate - 1, 'DD-MON-YY') AND to_date(sysdate, 'DD-MON-YY');
it does not give me the records (actualy i have any record on that date).
I try using 'in' operator, but still not give me the records.
Please throw some information for this.
*checker_date defined as date
a_horse_with_no_name :
Your first error is to call to_date() on a value that is already a DATE. to_date() expects a VARCHAR value, so sysdate will be first converted to VARCHAR and will then immediately be converted back to a DATE value which it was to begin with. \n\nYou probably want \n\nAND CHECKER_DATE BETWEEN trunc(sysdate) - 1 AND trunc(sysdate)\n\n\nMost probably this will still not give you want you want as that would not include rows from \"today\". trunc(sysdate) means \"today at midnight\" and any row that was created today after midnight will not be included. With date/time values (and Oracle's DATE type does contain a time, despite the name) it's better to not use BETWEEN, but explicit range operators instead:\n\nAND CHECKER_DATE >= trunc(sysdate) - 1 \nAND CHECKER_DATE < trunc(sysdate) + 1\n\n\ntrunc(sysdate) + 1 is tomorrow at midnight, so any value that is (strictly) smaller than that is \"today\". \n\n\n\nAll the above assumes that CHECKER_DATE is defined as DATE or TIMESTAMP",
2018-11-19T09:56:38
Gauravsa :
You can try:\n\nSELECT * \nFROM MY_VIEW \nWHERE STATUS = 'Active' \nAND CHECKER_DATE BETWEEN trunc(sysdate - 1) AND trunc(sysdate);\n\n\nOracle advises against using to_date for date. Also trunc is here because Trunc removes the time component. ",
2018-11-19T09:58:52